﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;//连接数据库并对其操作，添加命名空间
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Test1
{
    public partial class Form04 : Form
    {
        public Form04()
        {
            InitializeComponent();
        }

        #region 10-06
        String sql = "Server=DESKTOP-LG6OJK9;Database=db_EMS;User ID=sa;pwd=111111";
        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds;
        #endregion

        private void Form4_Load(object sender, EventArgs e)
        {
            #region 10-01
            /*String sql = "Server=DESKTOP-LG6OJK9;Database=db_EMS;User ID=sa;pwd=111111";//若有 转义字符“\” 需要改为“\\”
            SqlConnection con = new SqlConnection(sql);//等同10-04第一句
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                label1.Text = "SQL Server数据库连接开启";
                //con.Close();
            }
            if (con.State == ConnectionState.Closed)
            {
                label2.Text = "SQL Server数据库连接关闭"; 
            }*/
            #endregion

            #region 10-04
            SqlConnection con = new SqlConnection("Server=DESKTOP-LG6OJK9;Database=db_EMS;User ID=sa;pwd=111111");
            SqlCommand cmd = new SqlCommand("select * from tb_PDic order by ID asc", con);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();//SqlDataReader 获取数据
            richTextBox1.Text = "编号     版本     价格\n";
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    richTextBox1.Text += reader["ID"] + "     " + reader["Name"] + "  " + reader["Money"] + "\n";
                }
            }
            reader.Close();
            con.Close();
            #endregion

            #region 10-05
            //连接数据库见10-04第一句
            SqlDataAdapter da = new SqlDataAdapter("select*from tb_PDic", con);//自动打开和关闭数据库连接
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            #endregion

            #region 10-06
            dataGridView2.AllowUserToAddRows = false;//禁止添加行
            dataGridView2.AllowUserToDeleteRows = false;//禁止删除列
            con = new SqlConnection(sql);
            da = new SqlDataAdapter("Select * from tb_PDic", con);
            ds = new DataSet();
            da.Fill(ds);
            dataGridView2.DataSource = ds.Tables[0];
            for (int i = 0; i < dataGridView2.Columns.Count; i++)
                dataGridView2.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;//禁用排序功能
            dataGridView2.SelectionMode=DataGridViewSelectionMode.FullRowSelect;//单击选择一行
            foreach(DataGridViewRow row in dataGridView2.Rows)//颜色隔行显示不同
            {
                if (row.Index % 2 == 0)
                {
                    dataGridView2.Rows[row.Index].DefaultCellStyle.BackColor = Color.LightSalmon;
                }
                else
                {
                    dataGridView2.Rows[row.Index].DefaultCellStyle.BackColor = Color.LightPink;
                }
            }
            dataGridView2.DefaultCellStyle.SelectionBackColor = Color.LightBlue;//选中时的颜色
            dataGridView2.ReadOnly = true;//只读
            #endregion

            #region 10-07
            //不会 教程mdzz
            #endregion
        }
        #region 10-02,//03需要在数据库写好储存过程T_T  
        private void button1_Click(object sender, EventArgs e)
        {
            String sql = "Server=DESKTOP-LG6OJK9;Database=db_EMS;User ID=sa;pwd=111111";//转义字符“\”需要改为“\\”
            SqlConnection con = new SqlConnection(sql);
            SqlCommand cmd = new SqlCommand("insert into tb_PDic(Name,Money)values('" + textBox1.Text + "'," + Convert.ToDecimal(textBox2.Text) + ")", con);
            //T_T
            con.Open();
            if (cmd.ExecuteNonQuery() > 0)
                label5.Text = "添加成功!";
            else
                label5.Text = "添加失败！";
        }
        #endregion

        #region 10-06
        private void dataGridView2_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > 0)
            {
                int id = (int)dataGridView2.Rows[e.RowIndex].Cells[0].Value;
                con = new SqlConnection(sql);
                da = new SqlDataAdapter("select * from tb_PDic where ID =" + id, con);
                ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    textBox3.Text = ds.Tables[0].Rows[0][1].ToString();
                    textBox4.Text = ds.Tables[0].Rows[0][2].ToString();
                }
            }
        }
        #endregion


        #region 10-07
        //不会 教程mdzz
        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(sql);
            SqlCommand cmd = new SqlCommand("insert into tb_PDic(Name,Money)values('" + textBox3.Text + "'," + Convert.ToDecimal(textBox4.Text) + ")", con);
            con.Open();
            if (cmd.ExecuteNonQuery() > 0)
                label5.Text = "添加成功!";
            else
                label5.Text = "添加失败！";

        }

        private void button3_Click(object sender, EventArgs e)
        {

        }
        #endregion
    }
}
